/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package src.com.negocio.pedagogico;

import src.com.dados.pedagogico.ProfessorDao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
/**
 *
 * @author Patricia
 */

public class ConsultarProfessor {

public ArrayList<String> consultaProfessor(String nome, String i_idioma) throws SQLException {
        //System.out.print("AQ " + nome + i_idioma );
        String select_filtro = "SELECT distinct pes_nome, prof_idioma FROM pes_pessoa, prof_professor WHERE pes_id = pes_pessoa_pes_id ";
        String where = "";
        
        if (!nome.isEmpty()) {
            select_filtro += " and pes_nome = '" + nome + "';";
        } else {
            
            if (!i_idioma.equals(" ")) {
                where = where + " or prof_idioma like '" + i_idioma + "';";
            }
            //select_filtro = "SELECT tur_codigo,idm_descricao,pes_nome FROM tur_turma, idm_idioma,prof_professor,pes_pessoa,nvl_nivel where prof_professor_prof_id = prof_id and pes_pessoa_pes_id = prof_id and idm_idioma_idm_id = idm_id and nvl_nivel_nvl_id = nvl_id " + where;
        }
        
        select_filtro += where; 
        
        System.out.print(select_filtro);
        Connection conexao;
         ArrayList<String> result = new ArrayList<String>();


        String url = "jdbc:mysql://localhost/bdeng2";
        DriverManager.registerDriver(new com.mysql.jdbc.Driver());
        conexao = DriverManager.getConnection(url, "root", "dados");
        PreparedStatement stmt = conexao.prepareStatement(select_filtro);
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            result.add(rs.getString(1));
            result.add(rs.getString(2));
            //result.add(rs.getString(3));
        }

        conexao.close();
        return result;
    }
}
